package com.model;

import java.sql.*;
import java.util.ArrayList;

import com.javabean.User;

public class UserService {
	
	//根据用户的用户名返回用户相关的信息
	public User getUserByName(String userName){
		User user=new User();
		String sql="SELECT * FROM DocManSysDB.user where userName='"+userName+"'";
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            ResultSet rs=stat.executeQuery(sql);
            //System.out.println(rs.next());
            if(rs == null)
            	user = null;
            while(rs.next())
            {
            	user.setUserId(rs.getInt(1));
            	user.setUserName(rs.getString(2));
            	user.setUserPassword(rs.getString(3));
            	user.setUserEmail(rs.getString(4));
            	user.setUserGrade(rs.getInt(5));
            }
            
			//close work
            rs.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e2){
			e2.printStackTrace();
			return null;
		}
		
		return user;
	}
	
	public boolean registerUser(String userName,String userPassword,String userEmail){
		PreparedStatement stat = null;
		String sql = "INSERT INTO DocManSysDB.user (UserName,UserPassword,UserEmail) VALUES (?,?,?)";
		try {
			Connection con = DataBase.getConnection();
			stat = con.prepareStatement(sql);
			stat.setString(1,userName);
			stat.setString(2,userPassword);
			stat.setString(3,userEmail);
			
            stat.executeUpdate();
            //System.out.println(rs.next());
            
			//close work
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e2){
			e2.printStackTrace();
			return false;
		}
			
		return true;
	}
	//名字 分页功能需要返回的总页数
	/*
	public int getTotalPage(String choice,String input){
		int TotalPage=0;
		String sql="";
		if(choice.equals("姓名")){
	       sql="SELECT count(*) as total FROM campus_information.user where  UserName like '%"+input+"%'";}
		else if(choice.equals("所属部门")){
			sql="SELECT count(*) as total FROM campus_information.user where  UserDepartment like '%"+input+"%'";}
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            ResultSet rs=stat.executeQuery(sql);
            while(rs.next())
            {
            	TotalPage=rs.getInt("total");
            }
			//close work
			con.close();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		if(TotalPage%10==0){
			TotalPage=TotalPage/10;
		}else{
			TotalPage=TotalPage/10+1;
		}
		return TotalPage;
	}
	
	//有名字查询用户的功能
	public ArrayList<User> findUsers(String contend,int pageCountNow){
		ArrayList<User> users=new ArrayList<User>();
		int pageBefore=(pageCountNow-1)*10;
		String sql="SELECT * FROM campus_information.user where UserName like '%"+contend+"%' or UserName='"+contend+"' or UserName like '%"+contend+"%' limit "+pageBefore+",10";
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            ResultSet rs=stat.executeQuery(sql);
            while(rs.next())
            {
            	User user=new User();
            	user.setUserId(rs.getLong(1));
            	user.setUserName(rs.getString(2));
            	user.setUserDepartment(rs.getString(4));
            	user.setUserOffice(rs.getString(5));
            	user.setUserGrade(rs.getInt(7));
            	user.setUserNumber(rs.getString("userNumber"));
            	users.add(user);
            }
			//close work
			con.close();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
			return users;
	}
	//由部门查询用户
	public ArrayList<User> findUsersByDepartment(String contend,int pageCountNow){
		ArrayList<User> users=new ArrayList<User>();
		int pageBefore=(pageCountNow-1)*10;
		String sql="SELECT * FROM campus_information.user where UserDepartment like '%"+contend+"%' or UserDepartment='"+contend+"' or UserDepartment like '%"+contend+"%' limit "+pageBefore+",10";
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            ResultSet rs=stat.executeQuery(sql);
            while(rs.next())
            {
            	User user=new User();
            	user.setUserId(rs.getLong(1));
            	user.setUserName(rs.getString(2));
            	user.setUserDepartment(rs.getString(4));
            	user.setUserOffice(rs.getString(5));
            	user.setUserGrade(rs.getInt(7));
            	user.setUserNumber(rs.getString("userNumber"));
            	users.add(user);
            }
			//close work
			con.close();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
			return users;
	}
	//修改用户功能
	public boolean changeUser(User user){
		boolean isSucceed=false;
		String sql="update campus_information.user set  UserGrade='"+ user.getUserGrade()+"' ,UserId='"+user.getUserId()+"',UserDepartment='"+user.getUserDepartment() +"' ,UserOffice='"+ user.getUserOffice()+"'," +
				"UserDescription='"+ user.getUserDescription()+"'where UserId="+user.getUserId() +"";
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            int result=stat.executeUpdate(sql);
            if(result==1){
            	isSucceed=true;
            };
			//close work
			con.close();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
			return isSucceed;
	}
	//删除用户
	public boolean deleteUserById(int userId){
		boolean isSucceed=false;
		String sql="delete from campus_information.user WHERE UserId='"+ userId+"'";
		try {
			Connection con = DataBase.getConnection();
            Statement stat=con.createStatement();
            int result=stat.executeUpdate(sql);
            if(result==1){
            	isSucceed=true;
            };
			//close work
			con.close();
		} catch (Exception e2) {
			e2.printStackTrace();
		}
			return isSucceed;
	}
	//添加用户
		public boolean addUser(User user){
			boolean isSucceed=false;
			String sql="insert into user( UserName,UserPassword,UserDepartment,UserOffice,UserDescription,UserGrade,UserNumber)values('"+ user.getUserName()+"','"+ user.getUserPassword()+"','"+ user.getUserDepartment()+"'," +
					"'"+ user.getUserOffice()+"','"+ user.getUserDescription()+"',"+ user.getUserGrade()+",'"+ user.getUserNumber()+"')";
			try {
				Connection con = DataBase.getConnection();
	            Statement stat=con.createStatement();
	            int result=stat.executeUpdate(sql);
	            if(result==1){
	            	isSucceed=true;
	            };
				//close work
				con.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
				return isSucceed;
		}
		//修改用户权限
		public boolean changeGrade(int userGrade,String userName){
			boolean isSucceed=false;
			String sql="update campus_information.user set  UserGrade="+ userGrade+" where UserName='"+userName +"'";
			try {
				Connection con = DataBase.getConnection();
	            Statement stat=con.createStatement();
	            int result=stat.executeUpdate(sql);
	            if(result==1){
	            	isSucceed=true;
	            };
				//close work
				con.close();
			} catch (SQLException e2) {
				e2.printStackTrace();
			}
				return isSucceed;
		}
	*/
}